﻿USE [ReTS_Db]
GO 

PRINT ''
PRINT 'Creating stored procedures for Role_Type table...'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SelectRoleTypeById]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_SelectRoleTypeById]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_SelectRoleTypeById]
    @Role_Type_Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [Role_Type] WHERE Role_Type_Id = @Role_Type_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertRoleType]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_InsertRoleType]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_InsertRoleType]
	@Description nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Role_Type] 
	(
	Description
	)
VALUES 
	(
	@Description
	)

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UpdateRoleType]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_UpdateRoleType]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_UpdateRoleType]
    @Role_Type_Id int,
    @Description nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Role_Type] 
SET 
	Description = @Description
WHERE Role_Type_Id = @Role_Type_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteRoleType]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_DeleteRoleType]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_DeleteRoleType]
    @Role_Type_Id int
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM [Role_Type] 
WHERE Role_Type_Id = @Role_Type_Id

END
'
GO 